﻿CREATE PROCEDURE [dbo].[CollectGarbage]
(
	@Tries INT
)
AS
BEGIN
	-- First disable root queue
	ALTER QUEUE [RootQueue]  WITH ACTIVATION (STATUS = OFF);

	BEGIN TRANSACTION

	DECLARE @Service servicenametype
	DECLARE @Queue sysname
	DECLARE @MarkedForGCOutput TABLE([Service] servicenametype, [Queue] sysname)

	-- Lets pop a queue from de MarkedForGC table.
	DELETE TOP(1) [MarkedForGC] WITH (READPAST)
	 OUTPUT deleted.* INTO @MarkedForGCOutput

	SELECT  @Service = [Service]
		   ,@Queue = [Queue]
	FROM @MarkedForGCOutput
	

	-- Wait for the queue to empty, for a limited number of tries, we don't want to block the queues for a long time
	DECLARE @sql nvarchar(max)
	DECLARE @Count BIGINT

	BEGIN TRY

	WHILE @Tries > 0
	BEGIN
		SET @sql = N'SELECT @Count = COUNT(*) FROM [' + @Queue + ']';

		EXEC sp_executesql @sql, N'@Count BIGINT OUTPUT', @Count OUTPUT

		IF @Count = 0
		BEGIN
			
			
			DECLARE @Source servicenametype
			DECLARE @DialogHandle uniqueidentifier
			DECLARE @ServiceMetadataOutput TABLE ([Source] servicenametype, [Target] servicenametype, [DialogHandle] uniqueidentifier)

			DELETE [ServiceMetadata]
			OUTPUT deleted.[Source], deleted.[Target], deleted.[DialogHandle] INTO @ServiceMetadataOutput
			WHERE [Target] = @Service
						
			SELECT  @Source = [Source]
				   ,@DialogHandle = [DialogHandle]
			FROM @ServiceMetadataOutput

			END CONVERSATION @DialogHandle WITH CLEANUP;

			DELETE @ServiceMetadataOutput

			UPDATE [ServiceMetadata]
			SET [Source] = @Source
			OUTPUT inserted.[Source], inserted.[Target], inserted.[DialogHandle] INTO @ServiceMetadataOutput
			WHERE [Source] = @Service
			

			DECLARE dlgCursor CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY
			FOR 
			SELECT  [Source]
				   ,[Target]
				   ,[DialogHandle]
			FROM @ServiceMetadataOutput
			
			OPEN dlgCursor

			DECLARE @curSource servicenametype
			DECLARE @curTarget servicenametype
			DECLARE @curDialogHandle uniqueidentifier

			FETCH NEXT FROM dlgCursor INTO @curSource, @curTarget, @curDialogHandle

			WHILE @@FETCH_STATUS = 0 
			BEGIN
				END CONVERSATION @curDialogHandle WITH CLEANUP;

				SET @sql = N'BEGIN DIALOG @h FROM SERVICE [' + @curSource + N'] TO SERVICE ''' + @curTarget + ''''
				SET @DialogHandle = NULL
				EXEC sp_executesql @sql, N'@h uniqueidentifier OUTPUT', @DialogHandle OUTPUT

				UPDATE [ServiceMetadata]
				SET [DialogHandle] = @DialogHandle
				WHERE [Source] = @curSource AND [Target] = @curTarget

				FETCH NEXT FROM dlgCursor INTO @curSource, @curTarget, @curDialogHandle
			END

			SET @sql = N'DROP SERVICE [' + @Service + ']'
			EXEC sp_executesql @sql;

			DECLARE @SPName sysname

			SELECT @SPName = activation_procedure
			FROM sys.service_queues
			WHERE [name] = @Queue

			SET @sql = N'DROP QUEUE [' + @Queue + ']'
			EXEC sp_executesql @sql;

			SET @sql = N'DROP PROCEDURE ' + @SPName
			EXEC sp_executesql @sql;

			COMMIT TRANSACTION

			BREAK
		END

		WAITFOR DELAY '00:00:05';

		SET @Tries = @Tries - 1

		
	END

	END TRY
	BEGIN CATCH
		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;

		SELECT @ErrorMessage = ERROR_MESSAGE(),
			   @ErrorSeverity = ERROR_SEVERITY(),
			   @ErrorState = ERROR_STATE();

		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION

		RAISERROR (@ErrorMessage, -- Message text.
				   @ErrorSeverity, -- Severity.
				   @ErrorState -- State.
				   );
	END CATCH

	IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION

	ALTER QUEUE [RootQueue]  WITH ACTIVATION (STATUS = ON);

	
END
